-- 创建一个方法  参数为需要切割的字符串
create Function StrToTable(@str varchar(1000))
Returns @tableName Table
(
   str2table varchar(50)
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将编程一个表，这个表
Begin
set @str = @str+','
Declare @insertStr varchar(50) --截取后的第一个字符串
Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(',',@str)-1)
set @newstr = stuff(@str,1,charindex(',',@str),'')
Insert @tableName Values(@insertStr)
while(len(@newstr)>0)
begin
   set @insertStr = left(@newstr,charindex(',',@newstr)-1)
   Insert @tableName Values(@insertStr)
   set @newstr = stuff(@newstr,1,charindex(',',@newstr),'')
end
Return
End

-- 使用时传递需要查询的字符串即可
declare @str varchar(100);

select @str =  xzzxz from formtable_main_20 where id = 133

select * from hrmresource where id in (select str2table from StrToTable(@str) )





select * from (
select main20.gdbh,dt1.sybm,main20.xdrq,main20.khmcn,main20.lb,
	 (select lastname+' ' from hrmresource where id in (select str2table from StrToTable(main20.xzysz)) FOR XML PATH ('')) as xzysz,
	 (select lastname+' ' from hrmresource where id in (select str2table from StrToTable(main20.xzphz)) FOR XML PATH ('')) as xzphz,
	 (select lastname+' ' from hrmresource where id in (select str2table from StrToTable(main20.xzzxz)) FOR XML PATH ('')) as xzzxz,
	 (select lastname+' ' from hrmresource where id in (select str2table from StrToTable(main20.xzlpz)) FOR XML PATH ('')) as xzlpz,
	 (select pxbm from uf_kcbd u1 where dt1.pxbmn = u1.id) AS pxbm,
	 (select khmc from uf_khmc u where main20.khmcn=u.id) AS khmc,
	 (select lastname from hrmresource hrm where hrm.id=main20.xdr) AS xdrname,
	 dt1.xmcc,dt1.kuan,dt1.gao,dt1.cz,dt1.dw,dt1.bj,dt1.hjje,
	  main20.khjsrsr,main20.xdr,
	 (select selectname from workflow_SelectItem item where item.fieldid='6036' and item.selectvalue=main20.lb) as lbname 
from 
	formtable_main_20 main20, formtable_main_20_dt1 dt1 
where 
	main20.id=dt1.mainid
) a  where 1=1


